CREATE OR REPLACE EDITIONABLE PACKAGE BODY "SCOTT"."PKG_MOVING_AVERAGE" 
as

/***********************************************************************************************************************************

                                                            procedure

***********************************************************************************************************************************/

/* ----------------------------- insert the records of stocks ------------------------------------------------ */
procedure WRITE_STOCKS(stockDate in date, code in nvarchar2, stockOpen in number,
stockHigh in number, stockClose in number, stockLow in number, stockAmount in number) is
begin
    INSERT into STOCK_MOVING_AVERAGE(STOCK_DATE,STOCK_CODE,STOCK_OPEN,STOCK_HIGH,STOCK_CLOSE,STOCK_LOW,STOCK_AMOUNT)
    values(stockDate,code,stockOpen,stockHigh,stockClose,stockLow,stockAmount);
end WRITE_STOCKS;


/*---------------------------------------------------- calculate five moving average -------------------------------------------------*/
procedure CALCULATE_FIVE is
        begin
          ------------------------------------------ standard declare section ---------------------------------------------------
          declare
          -- 表示stock_code
          stockCode varchar2(10);
          -- 表示5天收盘价的和
          fiveSum number:=0;
          -- 表示5天收盘价的平均值
          fiveAverage number:=0;
          -- 定义一个含有5个数值型数据的数组
          type type_array is varray(5) of number;
          arrayFive type_array:=type_array();
          -- define cursor section.返回全部stock_code
          cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code asc;
          cursor allStockClose is select t.stock_close,t.stock_date from stock_moving_average t where t.stock_code=stockCode order by t.stock_date asc;
          ------------------------------------------ standard declare section ---------------------------------------------------

          begin
            --------------------------------- standard for loop section --------------------------------------------------------
            for i in allStockCode loop
                stockCode:=i.stock_code;
                dbms_output.put_line('**************   stockCode'||'  :  '||stockCode);
                arrayFive:=type_array();
                for j in allStockClose loop
                    arrayFive.extend;-- 扩展数组，扩展一个元素
                    arrayFive(arrayFive.count):=j.stock_close;
                    if mod(arrayFive.count,5)=0 then
                        dbms_output.put_line('j.stock_date'||'  :  '||j.stock_date);
                        fiveSum:=0;
                        for x in 1..arrayFive.count loop
                            -- 求5天收盘价的和
                            fiveSum:=fiveSum+arrayFive(x);
                        end loop;
                        dbms_output.put_line('fiveSum'||'  :  '||fiveSum);
                        -- 删除数组中的第一个元素，将其与4个元素向前挪一位，并删除下标为5的元素
                        for y in 1..arrayFive.count-1 loop
                            arrayFive(y):=arrayFive(y+1);
                        end loop;
                        arrayFive.trim;
                        -- 5天收盘价的平均值
                        fiveAverage:=fiveSum/5;
                        -- 向所有记录的FIVE列插入5天收盘价的平均值
                        update stock_moving_average t set t.five=round(fiveAverage,2) where t.stock_code=stockCode and t.stock_date=j.stock_date;
                        dbms_output.put_line('fiveAverage'||'  :  '||fiveAverage);
                    end if;
                end loop;
            end loop;
          end;
        end CALCULATE_FIVE;
        -- end procedure

/*---------------------------------------------------- calculate ten moving average -------------------------------------------------*/
procedure CALCULATE_TEN is
        begin
          ------------------------------------------ standard declare section ---------------------------------------------------
          declare
          -- 表示stock_code
          stockCode varchar2(10);
          -- 表示10天收盘价的和
          tenSum number:=0;
          -- 表示10天收盘价的平均值
          tenAverage number:=0;
          -- 定义一个含有10个数值型数据的数组
          type type_array is varray(10) of number;
          arrayTen type_array:=type_array();
          -- define cursor section.返回全部stock_code
          cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code asc;
          cursor allStockClose is select t.stock_close,t.stock_date from stock_moving_average t where t.stock_code=stockCode order by t.stock_date asc;
          ------------------------------------------ standard declare section ---------------------------------------------------

          begin
            --------------------------------- standard for loop section --------------------------------------------------------
            for i in allStockCode loop
                stockCode:=i.stock_code;
                dbms_output.put_line('**************   stockCode'||'  :  '||stockCode);
                arrayTen:=type_array();
                for j in allStockClose loop
                    arrayTen.extend;-- 扩展数组，扩展一个元素
                    arrayTen(arrayTen.count):=j.stock_close;
                    if mod(arrayTen.count,10)=0 then
                        dbms_output.put_line('j.stock_date'||'  :  '||j.stock_date);
                        tenSum:=0;
                        for x in 1..arrayTen.count loop
                            -- 求10天收盘价的和
                            tenSum:=tenSum+arrayTen(x);
                        end loop;
                        dbms_output.put_line('tenSum'||'  :  '||tenSum);
                        -- 删除数组中的第一个元素，将其与9个元素向前挪一位，并删除下标为10的元素
                        for y in 1..arrayTen.count-1 loop
                            arrayTen(y):=arrayTen(y+1);
                        end loop;
                        arrayTen.trim;
                        -- 10天收盘价的平均值
                        tenAverage:=tenSum/10;
                        -- 向所有记录的FIVE列插入10天收盘价的平均值
                        update stock_moving_average t set t.ten=round(tenAverage,2) where t.stock_code=stockCode and t.stock_date=j.stock_date;
                        dbms_output.put_line('tenAverage'||'  :  '||tenAverage);
                    end if;
                end loop;
            end loop;
          end;
        end CALCULATE_TEN;
        -- end procedure


/*---------------------------------------------------- calculate twenty moving average -------------------------------------------------*/
procedure CALCULATE_TWENTY is
        begin
          ------------------------------------------ standard declare section ---------------------------------------------------
          declare
          -- 表示stock_code
          stockCode varchar2(10);
          -- 表示20天收盘价的和
          twentySum number:=0;
          -- 表示20天收盘价的平均值
          twentyAverage number:=0;
          -- 定义一个含有20个数值型数据的数组
          type type_array is varray(20) of number;
          arrayTwenty type_array:=type_array();
          -- define cursor section.返回全部stock_code
          cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code asc;
          cursor allStockClose is select t.stock_close,t.stock_date from stock_moving_average t where t.stock_code=stockCode order by t.stock_date asc;
          ------------------------------------------ standard declare section ---------------------------------------------------

          begin
            --------------------------------- standard for loop section --------------------------------------------------------
            for i in allStockCode loop
                stockCode:=i.stock_code;
                dbms_output.put_line('**************   stockCode'||'  :  '||stockCode);
                arrayTwenty:=type_array();
                for j in allStockClose loop
                    arrayTwenty.extend;-- 扩展数组，扩展一个元素
                    arrayTwenty(arrayTwenty.count):=j.stock_close;
                    if mod(arrayTwenty.count,20)=0 then
                        dbms_output.put_line('j.stock_date'||'  :  '||j.stock_date);
                        twentySum:=0;
                        for x in 1..arrayTwenty.count loop
                            -- 求20天收盘价的和
                            twentySum:=twentySum+arrayTwenty(x);
                        end loop;
                        dbms_output.put_line('twentySum'||'  :  '||twentySum);
                        -- 删除数组中的第一个元素，将其与19个元素向前挪一位，并删除下标为20的元素
                        for y in 1..arrayTwenty.count-1 loop
                            arrayTwenty(y):=arrayTwenty(y+1);
                        end loop;
                        arrayTwenty.trim;
                        -- 20天收盘价的平均值
                        twentyAverage:=twentySum/20;
                        -- 向所有记录的FIVE列插入20天收盘价的平均值
                        update stock_moving_average t set t.twenty=round(twentyAverage,2) where t.stock_code=stockCode and t.stock_date=j.stock_date;
                        dbms_output.put_line('twentyAverage'||'  :  '||twentyAverage);
                    end if;
                end loop;
            end loop;
          end;
        end CALCULATE_TWENTY;
        -- end procedure


/*---------------------------------------------------- calculate sixty moving average -------------------------------------------------*/
procedure CALCULATE_SIXTY is
        begin
          ------------------------------------------ standard declare section ---------------------------------------------------
          declare
          -- 表示stock_code
          stockCode varchar2(10);
          -- 表示60天收盘价的和
          sixtySum number:=0;
          -- 表示60天收盘价的平均值
          sixtyAverage number:=0;
          -- 定义一个含有60个数值型数据的数组
          type type_array is varray(60) of number;
          arraySixty type_array:=type_array();
          -- define cursor section.返回全部stock_code
          cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code asc;
          cursor allStockClose is select t.stock_close,t.stock_date from stock_moving_average t where t.stock_code=stockCode order by t.stock_date asc;
          ------------------------------------------ standard declare section ---------------------------------------------------

          begin
            --------------------------------- standard for loop section --------------------------------------------------------
            for i in allStockCode loop
                stockCode:=i.stock_code;
                dbms_output.put_line('**************   stockCode'||'  :  '||stockCode);
                arraySixty:=type_array();
                for j in allStockClose loop
                    arraySixty.extend;-- 扩展数组，扩展一个元素
                    arraySixty(arraySixty.count):=j.stock_close;
                    if mod(arraySixty.count,60)=0 then
                        dbms_output.put_line('j.stock_date'||'  :  '||j.stock_date);
                        sixtySum:=0;
                        for x in 1..arraySixty.count loop
                            -- 求60天收盘价的和
                            sixtySum:=sixtySum+arraySixty(x);
                        end loop;
                        dbms_output.put_line('sixtySum'||'  :  '||sixtySum);
                        -- 删除数组中的第一个元素，将其与59个元素向前挪一位，并删除下标为60的元素
                        for y in 1..arraySixty.count-1 loop
                            arraySixty(y):=arraySixty(y+1);
                        end loop;
                        arraySixty.trim;
                        -- 60天收盘价的平均值
                        sixtyAverage:=sixtySum/60;
                        -- 向所有记录的FIVE列插入60天收盘价的平均值
                        update stock_moving_average t set t.sixty=round(sixtyAverage,2) where t.stock_code=stockCode and t.stock_date=j.stock_date;
                        dbms_output.put_line('sixtyAverage'||'  :  '||sixtyAverage);
                    end if;
                end loop;
            end loop;
          end;
        end CALCULATE_SIXTY;
        -- end procedure


/*---------------------------------------------------- calculate one hundred twenty moving average -------------------------------------------------*/
procedure CALCULATE_ONEHUNDREDTWENTY is
        begin
          ------------------------------------------ standard declare section ---------------------------------------------------
          declare
          -- 表示stock_code
          stockCode varchar2(10);
          -- 表示120天收盘价的和
          onehundredtwentySum number:=0;
          -- 表示120天收盘价的平均值
          onehundredtwentyAverage number:=0;
          -- 定义一个含有120个数值型数据的数组
          type type_array is varray(120) of number;
          arrayOnehundredtwenty type_array:=type_array();
          -- define cursor section.返回全部stock_code
          cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code asc;
          cursor allStockClose is select t.stock_close,t.stock_date from stock_moving_average t where t.stock_code=stockCode order by t.stock_date asc;
          ------------------------------------------ standard declare section ---------------------------------------------------

          begin
            --------------------------------- standard for loop section --------------------------------------------------------
            for i in allStockCode loop
                stockCode:=i.stock_code;
                dbms_output.put_line('**************   stockCode'||'  :  '||stockCode);
                arrayOnehundredtwenty:=type_array();
                for j in allStockClose loop
                    arrayOnehundredtwenty.extend;-- 扩展数组，扩展一个元素
                    arrayOnehundredtwenty(arrayOnehundredtwenty.count):=j.stock_close;
                    if mod(arrayOnehundredtwenty.count,120)=0 then
                        dbms_output.put_line('j.stock_date'||'  :  '||j.stock_date);
                        onehundredtwentySum:=0;
                        for x in 1..arrayOnehundredtwenty.count loop
                            -- 求120天收盘价的和
                            onehundredtwentySum:=onehundredtwentySum+arrayOnehundredtwenty(x);
                        end loop;
                        dbms_output.put_line('onehundredtwentySum'||'  :  '||onehundredtwentySum);
                        -- 删除数组中的第一个元素，将其与119个元素向前挪一位，并删除下标为120的元素
                        for y in 1..arrayOnehundredtwenty.count-1 loop
                            arrayOnehundredtwenty(y):=arrayOnehundredtwenty(y+1);
                        end loop;
                        arrayOnehundredtwenty.trim;
                        -- 120天收盘价的平均值
                        onehundredtwentyAverage:=onehundredtwentySum/120;
                        -- 向所有记录的FIVE列插入120天收盘价的平均值
                        update stock_moving_average t set t.one_hundred_twenty=round(onehundredtwentyAverage,2) where t.stock_code=stockCode and t.stock_date=j.stock_date;
                        dbms_output.put_line('onehundredtwentyAverage'||'  :  '||onehundredtwentyAverage);
                    end if;
                end loop;
            end loop;
          end;
        end CALCULATE_ONEHUNDREDTWENTY;
        -- end procedure


/*---------------------------------------------- calculate infinite moving average -----------------------------------------
procedure CALCULATE_INFINITE is
        begin
          ------------------------------------------ standard declare section ---------------------------------------------------
          declare
          -- 表示stock_code
          stockCode varchar2(10);
          -- 表示所有交易日收盘价的和
          infiniteSum number:=0;
          -- 表示所有交易日收盘价的平均值
          infiniteAverage number:=0;
          -- 定义一个含有无限个数值型数据的数组
          type type_array is table of number;
          arrayInfinite type_array:=type_array();
          -- define cursor section.返回全部stock_code
          cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code asc;
          cursor allStockClose is select t.stock_close,t.stock_date from stock_moving_average t where t.stock_code=stockCode order by t.stock_date asc;
          ------------------------------------------ standard declare section ---------------------------------------------------

          begin
            --------------------------------- standard for loop section --------------------------------------------------------
            for i in allStockCode loop
                stockCode:=i.stock_code;
                dbms_output.put_line('**************   stockCode'||'  :  '||stockCode);
                arrayInfinite:=type_array();
                infiniteSum:=0;
                infiniteAverage:=0;
                for j in allStockClose loop
                    arrayInfinite.extend;-- 扩展数组，扩展一个元素
                    arrayInfinite(arrayInfinite.count):=j.stock_close;
                    -- 求所有天收盘价的和
                    infiniteSum:=infiniteSum+arrayInfinite(arrayInfinite.count);
                    dbms_output.put_line('infiniteSum'||'  :  '||infiniteSum);
                    -- 所有天收盘价的平均值
                    infiniteAverage:=infiniteSum/arrayInfinite.count;
                    -- 向所有记录的FIVE列插入120天收盘价的平均值
                    update stock_moving_average t set t.infinite=round(infiniteAverage,2) where t.stock_code=stockCode and t.stock_date=j.stock_date;
                    dbms_output.put_line('infiniteAverage'||'  :  '||infiniteAverage);
                end loop;
            end loop;
          end;
        end CALCULATE_INFINITE;
        -- end procedure
*/

/*---------------------------------------------------- write moving average by date -------------------------------------------------*/
procedure WRITE_MOVING_AVERAGE_BY_DATE(stockDate in varchar2) is
        begin
          ------------------------------------------ standard declare section ---------------------------------------------------
          declare
          -- 表示stock_code
          stockCode varchar2(10);
          -- 表示5天收盘价的平均值
          fiveAverage number:=0;
          -- 表示10天收盘价的平均值
          tenAverage number:=0;
          -- 表示20天收盘价的平均值
          twentyAverage number:=0;
          -- 表示60天收盘价的平均值
          sixtyAverage number:=0;
          -- 表示120天收盘价的平均值
          oneHundredTwentyAverage number:=0;
          -- 表示所有天收盘价的平均值
          --infiniteAverage number:=0;
          -- define cursor section.返回全部stock_code
          cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code asc;
          ------------------------------------------ standard declare section ---------------------------------------------------

            begin
            --------------------------------- standard for loop section --------------------------------------------------------
              for i in allStockCode loop
                  stockCode:=i.stock_code;
                  dbms_output.put_line('**************   stockCode'||'  :  '||stockCode);

                  -- 更新所有股票某一天的5日均线
                  select avg(d.stock_close) into fiveAverage from(
                         select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=5 order by t.stock_date desc
                  ) d;
                  update stock_moving_average t set t.five=round(fiveAverage,2) where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');

                  -- 更新所有股票某一天的10日均线
                  select avg(d.stock_close) into tenAverage from(
                         select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=10 order by t.stock_date desc
                  ) d;
                  update stock_moving_average t set t.ten=round(tenAverage,2) where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');

                  -- 更新所有股票某一天的20日均线
                  select avg(d.stock_close) into twentyAverage from(
                         select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=20 order by t.stock_date desc
                  ) d;
                  update stock_moving_average t set t.twenty=round(twentyAverage,2) where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');

                  -- 更新所有股票某一天的60日均线
                  select avg(d.stock_close) into sixtyAverage from(
                         select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=60 order by t.stock_date desc
                  ) d;
                  update stock_moving_average t set t.sixty=round(sixtyAverage,2) where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');

                  -- 更新所有股票某一天的120日均线
                  select avg(d.stock_close) into oneHundredTwentyAverage from(
                         select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=120 order by t.stock_date desc
                  ) d;
                  update stock_moving_average t set t.one_hundred_twenty=round(oneHundredTwentyAverage,2) where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');

                  -- 更新所有股票某一天的所有日均线
                  /*select avg(d.stock_close) into infiniteAverage from(
                         select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') order by t.stock_date desc
                  ) d;
                  update stock_moving_average t set t.infinite=round(infiniteAverage,2) where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
                  */
              end loop;
          end;
        end WRITE_MOVING_AVERAGE_BY_DATE;
        -- end procedure



/*------------------------------------------------ select bull rank stock ------------------------------------------------------*/
procedure SELECT_BULL_RANK_STOCK(stockDate in varchar2,num out number) as
    -- define cursor section.返回全部在某一日均线成多头排列的股票
    cursor bullRankStockCode is select distinct * from stock_moving_average t
            where t.stock_close>=t.five and t.stock_close>=t.ten and t.stock_close>=t.twenty and t.stock_close>=t.sixty and t.stock_close>=t.one_hundred_twenty
            and t.five>=t.ten and t.five>=t.twenty and t.five>=t.sixty and t.five>=t.one_hundred_twenty
            and t.ten>=t.twenty and t.ten>=t.sixty and t.ten>=t.one_hundred_twenty
            and t.twenty>=t.sixty and t.twenty>=t.one_hundred_twenty
            and t.sixty>=t.one_hundred_twenty
            and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
begin
    num:=0;
    for i in bullRankStockCode loop
        dbms_output.put_line('stock code   '||i.stock_code);
        num:=num+1;
    end loop;
end SELECT_BULL_RANK_STOCK;

/*------------------------------------------------ select stock by three conditions ------------------------------------------------------*/
procedure SELECT_REVERSE_STOCK(stockDate in varchar2,percentage in number,resultStockCode out T_TYPE_ARRAY) as
    -- 表示stock_code
    stockCode varchar2(10);
    -- 表示某只股票历史上的最高收盘价
    maxStockClose number;
    -- 表示当前股票某一日的收盘价
    currentStockClose number;
    -- 表示当前股票某一日的120均线价格
    currentStockOneHundredTwenty number;
    -- 用于计算某只股票的120均线是否在20个交易日内不是单调递减的
    num number;
    -- 定义一个字符型数据的数组，用于存储被选中的股票的代码。TYPE_ARRAY的定义在Type目录中。
    --arrayStockCode type_array:=type_array(3000);
    -- define cursor section.返回全部在某一日均线成多头排列的股票
    cursor allStockCode is select distinct * from stock_moving_average t where t.stock_date=to_date(stockDate,'yyyy-mm-dd');
    -- define cursor section.返回全部在某一支股票在某一日之后（包括某一日）的20个交易日内的交易记录
    cursor topTwentyStockClose is select * from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd')) b where rownum<=20 order by b.stock_date desc;
    -- 创建UTL_FILE.file_type对象，用于读写文件
    file_handle UTL_FILE.file_type;
    begin
        --------------------------------- standard for loop section --------------------------------------------------------
        file_handle := UTL_FILE.FOPEN('TXTDIR','SELECT_REVERSE_STOCK.txt','w');
        resultStockCode:=t_type_array(5120);

        for i in allStockCode loop
            stockCode:=i.stock_code;
            -- 查询某只股票历史上的最高收盘价
            select max(t.stock_close) into maxStockClose from stock_moving_average t where t.stock_code=stockCode;
            -- 查询某只股票在某一日的收盘价格和120均线价格
            select t.stock_close,t.one_hundred_twenty into currentStockClose,currentStockOneHundredTwenty from stock_moving_average t where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');

            num:=0;
            -- 计算某只股票的某一日的收盘价格是否是其历史最高价的百分子：1-percentage
            if (maxStockClose-currentStockClose)/maxStockClose>percentage then
                for j in topTwentyStockClose loop
                    -- 判断某只股票的120均线是否在20个交易日内不是单调递减的
                    if currentStockOneHundredTwenty<j.one_hundred_twenty then
                        exit;
                    end if;

                    currentStockOneHundredTwenty:=j.one_hundred_twenty;
                    resultStockCode.extend;
                    num:=1+num;

                    if num=20 then
                        resultStockCode(resultStockCode.count):=j.stock_code;
                        --dbms_output.put_line('j.stock_code'||'  :  '||j.stock_code);
                        UTL_FILE.PUT_LINE(file_handle,j.stock_code);
                    end if;
                end loop;
            end if;
        end loop;
        UTL_FILE.FCLOSE(file_handle);
end SELECT_REVERSE_STOCK;
-- end procedure

/*------------------------------------------------ select stock by three conditions ------------------------------------------------------*/
procedure SELECT_STOCK_3(stockDate in varchar2,percentage in number) as
begin
    ------------------------------------------ standard declare section ---------------------------------------------------
    declare
    -- 表示stock_code
    stockCode varchar2(10);
    -- 表示某只股票历史上的最高收盘价
    maxStockClose number;
    -- 表示当前股票某一日的收盘价
    currentStockClose number;
    -- 表示当前股票某一日的120均线价格
    currentStockOneHundredTwenty number;
    -- 用于计算某只股票的120均线是否在20个交易日内不是单调递减的
    num number;
    -- 定义一个字符型数据的数组，用于存储被选中的股票的代码。TYPE_ARRAY的定义在Type目录中。
    arrayStockCode t_type_array:=t_type_array();
    -- define cursor section.返回全部在某一日均线成多头排列的股票
    cursor allStockCode is select distinct * from stock_moving_average t
            where t.stock_close>=t.five and t.stock_close>=t.ten and t.stock_close>=t.twenty and t.stock_close>=t.sixty and t.stock_close>=t.one_hundred_twenty
            and t.five>=t.ten and t.five>=t.twenty and t.five>=t.sixty and t.five>=t.one_hundred_twenty
            and t.ten>=t.twenty and t.ten>=t.sixty and t.ten>=t.one_hundred_twenty
            and t.twenty>=t.sixty and t.twenty>=t.one_hundred_twenty
            and t.sixty>=t.one_hundred_twenty
            and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
    -- define cursor section.返回全部在某一支股票在某一日之后（包括某一日）的20个交易日内的交易记录
    cursor topTwentyStockClose is select * from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd')) b where rownum<=20 order by b.stock_date desc;
    -- 创建UTL_FILE.file_type对象，用于读写文件
    file_handle UTL_FILE.file_type;
    ------------------------------------------ standard declare section ---------------------------------------------------

    begin
        --------------------------------- standard for loop section --------------------------------------------------------
        file_handle := UTL_FILE.FOPEN('TXTDIR','SELECT_STOCK_3.txt','w');

        for i in allStockCode loop
            stockCode:=i.stock_code;
            -- 查询某只股票历史上的最高收盘价
            select max(t.stock_close) into maxStockClose from stock_moving_average t where t.stock_code=stockCode;
            -- 查询某只股票在某一日的收盘价格和120均线价格
            select t.stock_close,t.one_hundred_twenty into currentStockClose,currentStockOneHundredTwenty from stock_moving_average t where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');

            num:=0;
            -- 计算某只股票的某一日的收盘价格是否是其历史最高价的百分子：1-percentage
            if (maxStockClose-currentStockClose)/maxStockClose>percentage then
                for j in topTwentyStockClose loop
                    -- 判断某只股票的120均线是否在20个交易日内不是单调递减的
                    if currentStockOneHundredTwenty<j.one_hundred_twenty then
                        exit;
                    end if;

                    currentStockOneHundredTwenty:=j.one_hundred_twenty;
                    arrayStockCode.extend;
                    num:=1+num;

                    if num=20 then
                        arrayStockCode(arrayStockCode.count):=j.stock_code;
                        dbms_output.put_line('j.stock_code'||'  :  '||j.stock_code);
                        UTL_FILE.PUT_LINE(file_handle,j.stock_code);
                    end if;
                end loop;
            end if;
        end loop;
        UTL_FILE.FCLOSE(file_handle);
    end;
end SELECT_STOCK_3;
-- end procedure



/*-------------------------------------- judge whether the close of stock is up or down --------------------------------------------*/
procedure WRITE_UP_DOWN as
begin
    ------------------------------------------ standard declare section ---------------------------------------------------
    declare
    -- 表示stock_code
    stockCode varchar2(10);
    -- 表示前一天的stock_close
    preStockClose number;
    -- 表示某只股票第一天的stock_close
    initStockClose number;
    -- define cursor section.返回全部stock_code。注意表STOCK_CODE的字段stock_code是大写的，所以要用函数lower
    cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code desc;
    -- 返回某只股票所有交易日的收盘价
    cursor singleStockClose is select * from stock_moving_average t where t.stock_code=stockCode order by t.stock_date asc;
   ------------------------------------------ standard declare section ---------------------------------------------------

    begin
        for i in allStockCode loop
              stockCode:=lower(i.stock_code);
              select t.stock_close into initStockClose from stock_moving_average t where t.stock_code=stockCode and rownum<=1 order by t.stock_date asc;
              preStockClose:=initStockClose;
              for j in singleStockClose loop
                  if preStockClose=j.stock_close then
                     --dbms_output.put_line('stockCode'||'  :  '||stockCode);
                     update stock_moving_average t set t.up_down=0 where t.stock_code=j.stock_code and t.stock_date=j.stock_date;
                  elsif preStockClose<j.stock_close then
                     update stock_moving_average t set t.up_down=1 where t.stock_code=j.stock_code and t.stock_date=j.stock_date;
                     --dbms_output.put_line('stockCode'||'  :  '||stockCode);
                  else
                     update stock_moving_average t set t.up_down=-1 where t.stock_code=j.stock_code and t.stock_date=j.stock_date;
                     --dbms_output.put_line('stockCode'||'  :  '||stockCode);
                  end if;
                  preStockClose:=j.stock_close;
              end loop;
          end loop;
    end;
end WRITE_UP_DOWN;


/*------------------------------ judge whether the close of stock is up or down according the given date ------------------------------------*/
procedure WRITE_UP_DOWN_BY_DATE(stockDate in varchar2) as
    -- 表示stock_code
    stockCode varchar2(10);
    -- 表示当前股票某一日的收盘价
    currentStockClose number;
    -- 表示当前股票某一日的日期
    currentStockDate Date;
    -- define cursor section.返回全部stock_code。
    cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code asc;
    -- 返回某只股票从某一日开始最近两天的交易记录
    cursor twoStock is select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=2 order by t.stock_date desc;
begin
        for i in allStockCode loop
            stockCode:=i.stock_code;
            -- 返回某只股票某一日的收盘价和日期
            select t.stock_close,t.stock_date into currentStockClose,currentStockDate from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=1 order by t.stock_date desc;
            for j in twoStock loop
                if currentStockDate!=j.stock_date then
                   if j.stock_close>currentStockClose then
                      update stock_moving_average t set t.up_down=-1 where t.stock_code=i.stock_code and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
                   elsif j.stock_close<currentStockClose then
                      update stock_moving_average t set t.up_down=1 where t.stock_code=i.stock_code and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
                   else
                      update stock_moving_average t set t.up_down=0 where t.stock_code=i.stock_code and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
                   end if;
               end if;
           end loop;
       end loop;
end WRITE_UP_DOWN_BY_DATE;



/*------------------------------------------------------ write MACD of all stocks ------------------------------------------------------*/
procedure WRITE_MACD_INIT as
   -- 表示stock_code
    stockCode varchar2(10);
    -- define cursor section.返回全部stock_code
    cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code asc;
    -- 获取每只股票第一个交易日的日期
    cursor firstStockDate is select t.stock_date from stock_moving_average t where t.stock_code=stockCode and rownum<=1 order by t.stock_date asc;
begin
     -- 初始化每只股票第一个交易日的ema12,ema26,dif和dea字段
     for i in allStockCode loop
         stockCode:=i.stock_code;
         for j in firstStockDate loop
             update stock_moving_average t set t.ema12=t.stock_close,t.ema26=t.stock_close,t.dif=0,t.dea=0 where t.stock_code=stockCode and t.stock_date=j.stock_date;
             commit;
         end loop;
     end loop;
end WRITE_MACD_INIT;

procedure WRITE_MACD_EMA as
    preEma12 number;
    preEma26 number;
    preDif number;
    preDea number;
    firstDate date;
   -- 表示stock_code
    stockCode varchar2(10);
    -- define cursor section.返回全部stock_code
    cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code asc;
    -- 获取每只股票第一个交易日的日期
    cursor firstStockDate is select t.stock_date from stock_moving_average t where t.stock_code=stockCode and rownum<=1 order by t.stock_date asc;
    -- 根据stockCode选出某只股票的除第一天以外的全部交易记录，按升序排列
    cursor allStock is select distinct * from stock_moving_average t where t.stock_code=stockCode and t.stock_date>firstDate order by t.stock_date asc;
begin
    -- 计算每只股票其余交易日的ema12,ema26,dif和dea字段
     for i in allStockCode loop
         stockCode:=i.stock_code;
         -- 用记录是第一个交易日的字段初始化相关变量
         for x in firstStockDate loop
             select t.ema12,t.ema26,t.dif,t.dea into preEma12,preEma26,preDif,preDea from stock_moving_average t where t.stock_code=stockCode and t.stock_date=x.stock_date;
         end loop;

         select t.stock_date into firstDate from stock_moving_average t where t.stock_code=stockCode and rownum<=1 order by t.stock_date asc;
         for j in allStock loop
             -- 对于其余交易日，更新ema12,ema26,dif和dea字段
             update stock_moving_average t set t.ema12=preEma12*11/13+j.stock_close*2/13,t.ema26=preEma26*25/27+j.stock_close*2/27 where t.stock_code=stockCode and t.stock_date=j.stock_date;
             commit;
             -- 用于计算下一个交易日时使用
             select t.ema12 into preEma12 from stock_moving_average t where t.stock_code=stockCode and t.stock_date=j.stock_date;
             select t.ema26 into preEma26 from stock_moving_average t where t.stock_code=stockCode and t.stock_date=j.stock_date;
         end loop;
     end loop;
end WRITE_MACD_EMA;

procedure WRITE_MACD_DIF as
    preEma12 number;
    preEma26 number;
    preDif number;
    preDea number;
    firstDate date;
   -- 表示stock_code
    stockCode varchar2(10);
    -- define cursor section.返回全部stock_code
    cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code asc;
    -- 获取每只股票第一个交易日的日期
    cursor firstStockDate is select t.stock_date from stock_moving_average t where t.stock_code=stockCode and rownum<=1 order by t.stock_date asc;
    -- 根据stockCode选出某只股票的除第一天以外的全部交易记录，按升序排列
    cursor allStock is select distinct * from stock_moving_average t where t.stock_code=stockCode and t.stock_date>firstDate order by t.stock_date asc;
begin
    -- 计算每只股票其余交易日的ema12,ema26,dif和dea字段
     for i in allStockCode loop
         stockCode:=i.stock_code;
         -- 用记录是第一个交易日的字段初始化相关变量
         for x in firstStockDate loop
             select t.ema12,t.ema26,t.dif,t.dea into preEma12,preEma26,preDif,preDea from stock_moving_average t where t.stock_code=stockCode and t.stock_date=x.stock_date;
         end loop;
         select t.stock_date into firstDate from stock_moving_average t where t.stock_code=stockCode and rownum<=1 order by t.stock_date asc;
         for j in allStock loop
             update stock_moving_average t set t.dif=t.ema12-t.ema26 where t.stock_code=stockCode and t.stock_date=j.stock_date;
             commit;
             select t.dif into preDif from stock_moving_average t where t.stock_code=stockCode and t.stock_date=j.stock_date;
         end loop;
     end loop;
end WRITE_MACD_DIF;

procedure WRITE_MACD_DEA as
    preEma12 number;
    preEma26 number;
    preDif number;
    preDea number;
    firstDate date;
   -- 表示stock_code
    stockCode varchar2(10);
    -- define cursor section.返回全部stock_code
    cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code asc;
    -- 获取每只股票第一个交易日的日期
    cursor firstStockDate is select t.stock_date from stock_moving_average t where t.stock_code=stockCode and rownum<=1 order by t.stock_date asc;
    -- 根据stockCode选出某只股票的除第一天以外的全部交易记录，按升序排列
    cursor allStock is select distinct * from stock_moving_average t where t.stock_code=stockCode and t.stock_date>firstDate order by t.stock_date asc;
begin
     -- 计算每只股票其余交易日的ema12,ema26,dif和dea字段
     for i in allStockCode loop
         stockCode:=i.stock_code;
         -- 用记录是第一个交易日的字段初始化相关变量
         for x in firstStockDate loop
             select t.ema12,t.ema26,t.dif,t.dea into preEma12,preEma26,preDif,preDea from stock_moving_average t where t.stock_code=stockCode and t.stock_date=x.stock_date;
         end loop;
         select t.stock_date into firstDate from stock_moving_average t where t.stock_code=stockCode and rownum<=1 order by t.stock_date asc;
         for j in allStock loop
             update stock_moving_average t set t.dea=preDea*8/10+t.dif*2/10 where t.stock_code=stockCode and t.stock_date=j.stock_date;
             commit;
             select t.dea into preDea from stock_moving_average t where t.stock_code=stockCode and t.stock_date=j.stock_date;
         end loop;
     end loop;
end WRITE_MACD_DEA;



/*-------------------------------- write MACD of all stocks by date -----------------------------------------------*/
procedure WRITE_MACD_EMA_BY_DATE(stockDate in varchar2) as
    preEma12 number;
    preEma26 number;
    preDea number;
    -- 表示stock_code
    stockCode varchar2(10);
    -- define cursor section.返回全部stock_code
    cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code asc;
    -- 获取某只股票最近两天的记录
    cursor singleStock is select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=2 order by t.stock_date desc;
begin
     for i in allStockCode loop
         stockCode:=i.stock_code;
         for j in singleStock loop
             if j.stock_date!=to_date(stockDate,'yyyy-mm-dd') then
                preEma12:=j.ema12;
                preEma26:=j.ema26;
                preDea:=j.dea;
                update stock_moving_average t set t.ema12=preEma12*11/13+j.stock_close*2/13,t.ema26=preEma26*25/27+j.stock_close*2/27 where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
                commit;
             end if;
         end loop;
     end loop;
end WRITE_MACD_EMA_BY_DATE;

procedure WRITE_MACD_DIF_BY_DATE(stockDate in varchar2) as
    preEma12 number;
    preEma26 number;
    preDea number;
    -- 表示stock_code
    stockCode varchar2(10);
    -- define cursor section.返回全部stock_code
    cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code asc;
    -- 获取某只股票最近两天的记录
    cursor singleStock is select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=2 order by t.stock_date desc;
begin
     for i in allStockCode loop
         stockCode:=i.stock_code;
         for j in singleStock loop
             if j.stock_date!=to_date(stockDate,'yyyy-mm-dd') then
                preEma12:=j.ema12;
                preEma26:=j.ema26;
                preDea:=j.dea;
                update stock_moving_average t set t.dif=t.ema12-t.ema26 where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
                commit;
             end if;
         end loop;
     end loop;
end WRITE_MACD_DIF_BY_DATE;

procedure WRITE_MACD_DEA_BY_DATE(stockDate in varchar2) as
    preEma12 number;
    preEma26 number;
    preDea number;
    -- 表示stock_code
    stockCode varchar2(10);
    -- define cursor section.返回全部stock_code
    cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code asc;
    -- 获取某只股票最近两天的记录
    cursor singleStock is select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=2 order by t.stock_date desc;
begin
     for i in allStockCode loop
         stockCode:=i.stock_code;
         for j in singleStock loop
             if j.stock_date!=to_date(stockDate,'yyyy-mm-dd') then
                preEma12:=j.ema12;
                preEma26:=j.ema26;
                preDea:=j.dea;
                update stock_moving_average t set t.dea=preDea*8/10+t.dif*2/10 where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
                commit;
             end if;
         end loop;
     end loop;
end WRITE_MACD_DEA_BY_DATE;



/*------------------------------ judge if MACD of the stock is the gold fork by date -----------------------------------------*/
procedure SELECT_MACD_UP_BELOW_ZERO(stockDate in varchar2,rate in number,stockMacdResult out T_TYPE_ARRAY) as
    -- 定义一个stock_moving_average%rowtype类型的变量
    stockRecord stock_moving_average%rowtype;
    -- 定义一个含有5个数值型数据的数组
    type type_array is varray(3) of stock_moving_average%rowtype;
    arrayDifDea type_array:=type_array();
    -- 表示stock_code
    stockCode varchar2(10);
    -- define cursor section.返回全部stock_code
    cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code asc;
    -- 获取某只股票某一日及之前一日的记录
    cursor lastTwoDayStockInfo is select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=2 order by t.stock_date desc;
    -- 创建UTL_FILE.file_type对象，用于读写文件
    file_handle UTL_FILE.file_type;
begin
    stockMacdResult:=T_TYPE_ARRAY();
    file_handle := UTL_FILE.FOPEN('TXTDIR','SELECT_MACD_UP_BELOW_ZERO.txt','w');

     for i in allStockCode loop
         stockCode:=i.stock_code;
         arrayDifDea:=type_array();
         for j in lastTwoDayStockInfo loop
             stockRecord.dif:=j.dif;
             stockRecord.dea:=j.dea;
             stockRecord.stock_code:=j.stock_code;
             arrayDifDea.extend;
             arrayDifDea(arrayDifDea.count):=stockRecord;
         end loop;
         -- 由于有些股票是在stockDate之后才上市交易，因此没有记录，即arrayDifDea.count!=2，所以此处要先判断一下
         if arrayDifDea.count=2 then
             if arrayDifDea(1).dif>arrayDifDea(1).dea and arrayDifDea(2).dif<arrayDifDea(2).dea then
                if (arrayDifDea(1).dif+arrayDifDea(1).dea+arrayDifDea(2).dif+arrayDifDea(2).dea)/4<rate then
                   dbms_output.put_line(arrayDifDea(1).stock_code);
                   stockMacdResult.extend;
                   stockMacdResult(stockMacdResult.count):=arrayDifDea(1).stock_code;
                   UTL_FILE.PUT_LINE(file_handle,arrayDifDea(1).stock_code);
                end if;
             end if;
         end if;
     end loop;
     UTL_FILE.FCLOSE(file_handle);
end SELECT_MACD_UP_BELOW_ZERO;




/*-------------------------------- calculate the percentage of stock price up or down----------------------------------------*/
procedure CALCULATE_UP_DOWN_PERCENTAGE as
     -- 表示stock_code
     stockCode varchar2(10);
     -- 用来记录某只股票的第一条记录
     initStockRecord stock_moving_average%rowtype;
     -- define cursor section.返回全部stock_code
     cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code asc;
     -- 返回某一只股票除第一条记录以外的其他记录，并按生序排列
     cursor singleStockOtherRecord is select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date!=initStockRecord.stock_date order by t.stock_date asc;
begin
     for i in allStockCode loop
          stockCode:=i.stock_code;

          -- 计算某一只股票today_up_down_percentage字段的值
          select * into initStockRecord from stock_moving_average t where t.stock_code=i.stock_code and rownum<=1 order by t.stock_date asc;
          for j in singleStockOtherRecord loop
              update stock_moving_average t set t.today_up_down_percentage=round((j.stock_close-initStockRecord.stock_close)/initStockRecord.stock_close,4)*100 where t.stock_code=i.stock_code and t.stock_date=j.stock_date;
              initStockRecord.stock_close:=j.stock_close;
              commit;
          end loop;
     end loop;
end CALCULATE_UP_DOWN_PERCENTAGE;



/*----------------- calculate the volatility of stock close price with five day,ten day and twenty day -----------------------*/
procedure CALCULATE_VOLATILITY as
     -- 表示stock_code
     stockCode varchar2(10);
     -- 表示stock_date
     stockDate date;
     -- 表示五日内波动的总数
     fiveDayVolatilitySum number;
     -- 表示十日内波动的总数
     tenDayVolatilitySum number;
     -- 表示二十五日内波动的总数
     twentyDayVolatilitySum number;
     -- 计算五日内波动时的前一条记录
     initFiveDayStockRecord stock_moving_average%rowtype;
     -- 计算十日内波动时的前一条记录
     initTenDayStockRecord stock_moving_average%rowtype;
     -- 计算二十日内波动时的前一条记录
     initTwentyDayStockRecord stock_moving_average%rowtype;
     -- 返回全部stock_code
     cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code asc;
     -- 返回全部stock_date
     cursor allStockDate is select distinct t.stock_date from stock_moving_average t where t.stock_code=stockCode order by t.stock_date desc;
     -- 用于获取从指定日期开始五日内的记录，并按降序排列
     cursor nextFiveRecord is select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<initFiveDayStockRecord.stock_date and rownum<=5 order by t.stock_date desc;
     -- 用于获取从指定日期开始十日内的记录，并按降序排列
     cursor nextTenRecord is select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<initTenDayStockRecord.stock_date and rownum<=10 order by t.stock_date desc;
     -- 用于获取从指定日期开始二十日内的记录，并按降序排列
     cursor nextTwentyRecord is select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<initTwentyDayStockRecord.stock_date and rownum<=20 order by t.stock_date desc;
begin
     --- 计算字段five_day_volatility
     fiveDayVolatilitySum:=0;
     for i in allStockCode loop
          stockCode:=i.stock_code;
          for j in allStockDate loop
              stockDate:=j.stock_date;
              select * into initFiveDayStockRecord from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=stockDate and rownum<=1 order by t.stock_date desc;
              for x in nextFiveRecord loop
                  fiveDayVolatilitySum:=fiveDayVolatilitySum+abs((initFiveDayStockRecord.stock_close-x.stock_close)/x.stock_close);
                  if nextFiveRecord%ROWCOUNT=5 then
                     update stock_moving_average t set t.five_day_volatility=round(fiveDayVolatilitySum/5,4)*100 where t.stock_code=stockCode and t.stock_date=stockDate;
                     commit;
                  end if;
                  initFiveDayStockRecord.stock_close:=x.stock_close;
              end loop;
              fiveDayVolatilitySum:=0;
          end loop;
     end loop;

     --- 计算字段ten_day_volatility
     tenDayVolatilitySum:=0;
     for i in allStockCode loop
          stockCode:=i.stock_code;
          for j in allStockDate loop
              stockDate:=j.stock_date;
              select * into initTenDayStockRecord from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=stockDate and rownum<=1 order by t.stock_date desc;
              for x in nextTenRecord loop
                  tenDayVolatilitySum:=tenDayVolatilitySum+abs((initTenDayStockRecord.stock_close-x.stock_close)/x.stock_close);
                  if nextTenRecord%ROWCOUNT=10 then
                     update stock_moving_average t set t.ten_day_volatility=round(tenDayVolatilitySum/10,4)*100 where t.stock_code=stockCode and t.stock_date=stockDate;
                     commit;
                  end if;
                  initTenDayStockRecord.stock_close:=x.stock_close;
              end loop;
              tenDayVolatilitySum:=0;
          end loop;
     end loop;

     --- 计算字段twenty_day_volatility
     twentyDayVolatilitySum:=0;
     for i in allStockCode loop
          stockCode:=i.stock_code;
          for j in allStockDate loop
              stockDate:=j.stock_date;
              select * into initTwentyDayStockRecord from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=stockDate and rownum<=1 order by t.stock_date desc;
              for x in nextTwentyRecord loop
                  twentyDayVolatilitySum:=twentyDayVolatilitySum+abs((initTwentyDayStockRecord.stock_close-x.stock_close)/x.stock_close);
                  if nextTwentyRecord%ROWCOUNT=20 then
                     update stock_moving_average t set t.twenty_day_volatility=round(twentyDayVolatilitySum/20,4)*100 where t.stock_code=stockCode and t.stock_date=stockDate;
                     commit;
                  end if;
                  initTwentyDayStockRecord.stock_close:=x.stock_close;
              end loop;
              twentyDayVolatilitySum:=0;
          end loop;
     end loop;
end CALCULATE_VOLATILITY;



/*------------------------- calculate the correlation rate of a certain stock with several index -------------------------------*/
procedure CALCULATE_CORRELATION_RATE(stockCode in varchar2) as
     -- 用于写文件的变量
     fHandle utl_file.file_type;
     -- 表示上证指数，深证成指，中小板指数和创业板指数
     shIndexCode constant varchar2(8):='sh000001';
     szIndexCode constant varchar2(8):='sz399001';
     zxbIndexCode constant varchar2(8):='sz399005';
     cybIndexCode constant varchar2(8):='sz399006';
     -- 指定股票的最近10天的数据
     cursor lastFiveStockInfo is select * from stock_moving_average t where t.stock_code=stockCode and rownum<=10 order by t.stock_date desc;
     lastFiveUpNumber number;
     -- 上证指数，深证成指，中小板指数和创业板指数的最近10天的数据
     cursor lastFiveShIndexInfo is select * from stock_index t where t.index_code=shIndexCode and rownum<=10 order by t.index_date desc;
     cursor lastFiveSzIndexInfo is select * from stock_index t where t.index_code=szIndexCode and rownum<=10 order by t.index_date desc;
     cursor lastFiveZxbIndexInfo is select * from stock_index t where t.index_code=zxbIndexCode and rownum<=10 order by t.index_date desc;
     cursor lastFiveCybIndexInfo is select * from stock_index t where t.index_code=cybIndexCode and rownum<=10 order by t.index_date desc;
begin
     for i in lastFiveStockInfo loop
         if i.up_down=1 then
            lastFiveUpNumber:=lastFiveUpNumber+1;
         end if;
     end loop;
end CALCULATE_CORRELATION_RATE;



/*------------------------------------------------ 计算某一日所有股票的涨跌幅度 ------------------------------------------------*/
procedure CAL_UP_DOWN_PERCENTAGE_BY_DATE(stockDate in varchar2) as
     -- 表示stock_code
     stockCode varchar2(10);
     -- define cursor section.返回全部stock_code
     cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code asc;
     -- 表示某一只股票某一天的记录
     cursor singleStockByDate is select * from stock_moving_average t where t.stock_date=to_date(stockDate,'yyyy-mm-dd') and t.stock_code=stockCode;
     -- 表示某一只股票某一天之前的一天的记录
     cursor singleStockLastDate is select * from stock_moving_average t where t.stock_date<to_date(stockDate,'yyyy-mm-dd') and t.stock_code=stockCode and rownum<=1 order by t.stock_date desc;
begin
     for i in allStockCode loop
         stockCode:=i.stock_code;
         for j in singleStockByDate loop
             for x in singleStockLastDate loop
                 update stock_moving_average t set t.today_up_down_percentage=round((j.stock_close-x.stock_close)/x.stock_close,4)*100 where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
                 commit;
             end loop;
         end loop;
     end loop;
end CAL_UP_DOWN_PERCENTAGE_BY_DATE;



/*------------ 计算某一日所有股票价格的 -----------*/
procedure CAL_VOLATILITY_BY_DATE(stockDate in varchar2) as
     -- 表示stock_code
     stockCode varchar2(10);
     -- 表示五日内波动的总数
     fiveDayVolatilitySum number;
     -- 表示十日内波动的总数
     tenDayVolatilitySum number;
     -- 表示二十五日内波动的总数
     twentyDayVolatilitySum number;
     -- 计算五日内波动时的前一条记录
     initFiveDayStockRecord stock_moving_average%rowtype;
     -- 计算十日内波动时的前一条记录
     initTenDayStockRecord stock_moving_average%rowtype;
     -- 计算二十日内波动时的前一条记录
     initTwentyDayStockRecord stock_moving_average%rowtype;
     --  由于存在停牌的股票，所以stockCount(用于记录某一日，某一只股票的数量)等于0时，表示该停牌
     stockCount number;
     -- 返回全部stock_code
     cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code asc;
     -- 用于返回某日之后5天的记录（不包括某日）
     cursor lastFiveRecordByDate is select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<to_date(stockDate,'yyyy-mm-dd') and rownum<=5 order by t.stock_date desc;
     -- 用于返回某日之后10天的记录（不包括某日）
     cursor lastTenRecordByDate is select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<to_date(stockDate,'yyyy-mm-dd') and rownum<=10 order by t.stock_date desc;
     -- 用于返回某日之后20天的记录（不包括某日）
     cursor lastTwentyRecordByDate is select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<to_date(stockDate,'yyyy-mm-dd') and rownum<=20 order by t.stock_date desc;
begin
     --- 计算字段five_day_volatility
     fiveDayVolatilitySum:=0;
     for i in allStockCode loop
         stockCode:=i.stock_code;
         select count(*) into stockCount from stock_moving_average t where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
         if stockCount<>0 then
            select * into initFiveDayStockRecord from stock_moving_average t where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
            for j in lastFiveRecordByDate loop
                fiveDayVolatilitySum:=fiveDayVolatilitySum+abs((initFiveDayStockRecord.stock_close-j.stock_close)/j.stock_close);
                if lastFiveRecordByDate%ROWCOUNT=5 then
                   update stock_moving_average t set t.five_day_volatility=round(fiveDayVolatilitySum/5,4)*100 where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
                   commit;
                end if;
                initFiveDayStockRecord.stock_close:=j.stock_close;
            end loop;
         fiveDayVolatilitySum:=0;
         end if;
     end loop;

     --- 计算字段ten_day_volatility
     tenDayVolatilitySum:=0;
     for i in allStockCode loop
         stockCode:=i.stock_code;
         select count(*) into stockCount from stock_moving_average t where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
         if stockCount<>0 then
             select * into initTenDayStockRecord from stock_moving_average t where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
             for j in lastTenRecordByDate loop
                 tenDayVolatilitySum:=tenDayVolatilitySum+abs((initTenDayStockRecord.stock_close-j.stock_close)/j.stock_close);
                 if lastTenRecordByDate%ROWCOUNT=10 then
                    update stock_moving_average t set t.ten_day_volatility=round(tenDayVolatilitySum/10,4)*100 where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
                    commit;
                 end if;
                 initTenDayStockRecord.stock_close:=j.stock_close;
             end loop;
             tenDayVolatilitySum:=0;
         end if;
     end loop;

     --- 计算字段twenty_day_volatility
     twentyDayVolatilitySum:=0;
     for i in allStockCode loop
         stockCode:=i.stock_code;
         select count(*) into stockCount from stock_moving_average t where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
         if stockCount<>0 then
             select * into initTwentyDayStockRecord from stock_moving_average t where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
             for j in lastTwentyRecordByDate loop
                 twentyDayVolatilitySum:=twentyDayVolatilitySum+abs((initTwentyDayStockRecord.stock_close-j.stock_close)/j.stock_close);
                 if lastTwentyRecordByDate%ROWCOUNT=20 then
                    update stock_moving_average t set t.twenty_day_volatility=round(twentyDayVolatilitySum/20,4)*100 where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
                    commit;
                 end if;
                 initTwentyDayStockRecord.stock_close:=j.stock_close;
             end loop;
             twentyDayVolatilitySum:=0;
         end if;
     end loop;
end CAL_VOLATILITY_BY_DATE;




/*-------------------------------------------------- 查找某一日价格出现异动的股票 -----------------------------------------------*/
procedure FIND_ABNORMAL_STOCK(stockDate in varchar2,rate in number,stockResultArray out T_STOCK_RESULT_ARRAY) as
     -- 表示stock_code
     stockCode varchar2(10);
     -- T_STOCK_RESULT类型对象。表示出现异动的股票
     stockResult T_STOCK_RESULT;
     -- 返回全部stock_code
     cursor allStockCode is select distinct t.stock_code from stock_moving_average t /*where t.stock_code='sh600094'*/ order by t.stock_code asc;
     -- 某一只股票，某一天的记录
     cursor singleStockRecord is select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
     -- 创建UTL_FILE.file_type对象，用于读写文件
     file_handle UTL_FILE.file_type;
begin
     file_handle := UTL_FILE.FOPEN('TXTDIR','FIND_ABNORMAL_STOCK.txt','w');
     stockResultArray:=T_STOCK_RESULT_ARRAY();

     for i in allStockCode loop
         stockCode:=i.stock_code;
         for j in singleStockRecord loop
             if singleStockRecord%FOUND then
                 if j.today_up_down_percentage is not null and j.five_day_volatility is not null and j.ten_day_volatility is not null and j.twenty_day_volatility is not null then
                    if abs(j.today_up_down_percentage)>=j.five_day_volatility*rate and abs(j.today_up_down_percentage)>=j.ten_day_volatility*rate and abs(j.today_up_down_percentage)>=j.twenty_day_volatility*rate then
                       dbms_output.put_line(j.stock_code||'   '||j.stock_date);

                       stockResult:=T_STOCK_RESULT(j.stock_code,j.stock_date);
                       stockResultArray.extend;
                       stockResultArray(stockResultArray.count):=stockResult;

                       UTL_FILE.PUT_LINE(file_handle,stockResultArray(stockResultArray.count).getStockCode()||'   '||stockResultArray(stockResultArray.count).getStockDate());
                    end if;
                 end if;
             end if;
         end loop;
     end loop;
end FIND_ABNORMAL_STOCK;




/*---------------------------------------------------- test -------------------------------------------------*/
procedure TEST(stockDate in varchar2) as
        begin
          ------------------------------------------ standard declare section ---------------------------------------------------
          declare
          -- 表示stock_code
          stockCode varchar2(10);
          -- 某一只股票某一日的记录
          singleStockOneRecord stock_moving_average%rowtype;
          -- define cursor section.返回全部stock_code
          cursor allStockCode is select distinct t.stock_code from stock_moving_average t order by t.stock_code asc;
          -- 某一只股票某一日（不包括这一日）之后所有日期的所有记录
          cursor singleStockRecord is select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<to_date(stockDate,'yyyy-mm-dd') and rownum<=1 order by t.stock_date desc;
          ------------------------------------------ standard declare section ---------------------------------------------------
          begin
            --------------------------------- standard for loop section --------------------------------------------------------
              for i in allStockCode loop
                  stockCode:=i.stock_code;
                  select * into singleStockOneRecord from stock_moving_average t where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
                  for j in singleStockRecord loop
                      dbms_output.put_line(j.stock_code||'   11111'||singleStockOneRecord.today_up_down_percentage);
                      dbms_output.put_line(j.stock_date||'   22222'||j.today_up_down_percentage);
                      if j.today_up_down_percentage<>0 and singleStockOneRecord.today_up_down_percentage/j.today_up_down_percentage>2 then
                          dbms_output.put_line(j.stock_code);
                      end if;
                  end loop;
              end loop;
          end;
end TEST;
-- end procedure


end pkg_moving_average;